/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package data.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import data.DAO.services.PostDAOServices;
import model.entity.Comment;
import model.entity.Image;
import model.entity.Post;

/**
 *
 * @author ninkuhack
 */
public class PostDAO implements PostDAOServices {

    private static PostDAO postDAO;

    public static PostDAO getInstance() {
        if (postDAO == null) {
            postDAO = new PostDAO();
        }
        return postDAO;
    }
  
    @Override
    public boolean createPost(Post post) {
        try {
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("INSERT INTO posts(title,content,"
                    + "address,city,vote,price,user_id,status,created_date) VALUES( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?)");
            prs.setString(1, post.getTitle());
            prs.setString(2, post.getContent());
            prs.setString(3, post.getAddress());
            prs.setString(4, post.getCity());
            prs.setInt(5, post.getVote());
            prs.setDouble(6, post.getPrice());
            prs.setInt(7, post.getUser_id().getUserID());
            prs.setInt(8, post.getStatus());
            prs.setDate(9, post.getCreated_date());
            prs.executeUpdate();
            return true;
        } catch (Exception ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean editPost(Post post) {
        try {
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("UPDATE posts SET title = ?, content = ?, address = ?, city = ?, vote = ?, price = ?, user_id = ?, status = ?, created_date = ? WHERE post_id = ?");
            prs.setString(1, post.getTitle());
            prs.setString(2, post.getContent());
            prs.setString(3, post.getAddress());
            prs.setString(4, post.getCity());
            prs.setInt(5, post.getVote());
            prs.setDouble(6, post.getPrice());
            prs.setInt(7, post.getUser_id().getUserID());
            prs.setInt(8, post.getStatus());
            prs.setDate(9, post.getCreated_date());
            prs.setInt(10, post.getPost_id());
            prs.executeUpdate();
            return true;
        } catch (Exception ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    @Override
    public boolean setStatusPost(int post_id, int status) {
        try {
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("UPDATE posts SET status = ? WHERE post_id = ?");
            prs.setInt(1, status);
            prs.setInt(2, post_id);
            prs.executeUpdate();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    private Post createNewPost(ResultSet rs) throws SQLException{
        Post post = new Post();
        post.setAddress(rs.getString("address"));
        post.setCity(rs.getString("city"));
        post.setContent(rs.getString("content"));
        post.setCreated_date(rs.getDate("created_date"));
        post.setPost_id(rs.getInt("post_id"));
        post.setPrice(rs.getDouble("price"));
        post.setStatus(rs.getInt("status"));
        post.setTitle(rs.getString("title"));
        post.setUser_id(UserDAOServiceImpl.getInstance().getUserByID(rs.getInt("user_id")));
        post.setVote(rs.getInt("vote"));
        return post;
    }
   
    @Override
    public List<Post> getAllPost() {
        try {
            List<Post> postList = new ArrayList<Post>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts where status = 0 ORDER BY created_date DESC");
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (SQLException ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public List<Post> getPostByUserId(int user_id) {
        try {
            List<Post> postList = new ArrayList<Post>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE user_id = ? and status = 0");
            prs.setInt(1, user_id);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (SQLException ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public List<Post> getPostByCreatedDate(Date created_date) {
        try {
            List<Post> postList = new ArrayList<Post>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE created_date = ? and status = 0");
            prs.setDate(1, created_date);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (SQLException ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public List<Post> getPostByCity(String city) {
        try {
            List<Post> postList = new ArrayList<>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE city = ? status = 0");
            prs.setString(1, city);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Post> getPostByStatus(int status) {
        try {
            List<Post> postList = new ArrayList<>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE status = ?");
            prs.setInt(1, status);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Post> getPostByAddress(String address) {
        try {
            List<Post> postList = new ArrayList<>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE address = ? and status = 0");
            prs.setString(1, address);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Post> getPostByVoteDesc(int vote) {
        try {
            List<Post> postList = new ArrayList<>();
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("SELECT * FROM Posts WHERE vote = ? and status = 0");
            prs.setInt(1, vote);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                postList.add(createNewPost(rs));
            }
            return postList;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public Post getPostByID(int post_id) {
        Connection cn = null;
        Post post = new Post();
        try {
            String strSql = "select * from posts where post_id = ? and status = 0";
            cn = ConnectionManager.getConnection();
            PreparedStatement statement = cn.prepareStatement(strSql);
            statement.setInt(1, post_id);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                post = createNewPost(rs);
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
        return post;
    }

    @Override
    public boolean deletePost(int post_id) {
        boolean flag = false;
        try {
            Connection conn = ConnectionManager.getConnection();
            PreparedStatement prs = conn.prepareStatement("update posts set status = 1 where post_id = ?");
            prs.setInt(1, post_id);
            int row = prs.executeUpdate();
            if(row >= 0){
                flag = true;
            }
            
        } catch (Exception ex) {
            Logger.getLogger(PostDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return flag;
    }
}
